In [ ]:
import math
import pandas as pd
import json
from matplotlib import path
import numpy as np

h_filename='BVG/batch_result_20170710/haltestellen_20170710.csv'
hdf=pd.read_csv(h_filename,sep=';',encoding = "ISO-8859-1")
hdf.columns=['H','lon','lat','location']
dictH={}
for row in hdf.itertuples(index=True):
    dictH[row.location]={'lon':row.lon,'lat':row.lat}
stations=dictH.keys()

q_filename='BVG/batch_result_20170710/anfragen_20170710.csv'
qdf=pd.read_csv(q_filename,sep=';',encoding = "ISO-8859-1")
a=qdf[['ANFRAGE_ID','START_NAME','START_X','START_Y']]
b=qdf[['ANFRAGE_ID','ZIEL_NAME','ZIEL_X','ZIEL_Y']]
columns=['PathID','location','lon','lat']
a.columns=columns
b.columns=columns
df=pd.concat([a,b])
lonL=[]
latL=[]
stations=dictH.keys()
cnt1=0
cnt2=0
for row in df.itertuples(index=True):
    index=row.Index
    if math.isnan(row.lon):
        cnt1+=1
        if row.location in stations:
            lonL.append(dictH[row.location]['lon'])
            latL.append(dictH[row.location]['lat'])
        else:
            cnt2+=1
            lonL.append(float('nan'))
            latL.append(float('nan'))
    else:
        lonL.append(row.lon)
        latL.append(row.lat)
df['lon']=lonL
df['lat']=latL
df.dropna(inplace=True)

geoData=json.load(open('bezirksgrenzen.geojson'))
regionsPDict={}
for i in range(len(geoData['features'])):
    tmpDf=pd.DataFrame(data=geoData['features'][i]['geometry']['coordinates'][0][0],columns=['lon','lat'])
    region_id=int(geoData['features'][i]['properties']['Gemeinde_schluessel'])
    tmpDf['region_id']=region_id
    tmpDf['region_name']=geoData['features'][i]['properties']['Gemeinde_name']
    if i==0:
        regionsDF=tmpDf
    else:
        regionsDF=pd.concat([regionsDF,tmpDf])
        
    regionsPDict[region_id]=path.Path(geoData['features'][i]['geometry']['coordinates'][0][0])
    

#df[regionsPDict[1].contains_points(df[['lon','lat']])]
dfDict={}
regionsList=np.arange(1,13)
connectivityG=np.zeros(shape=(len(regionsList),len(regionsList)),dtype=int)

for r1ID in regionsList:
    dfDict[r1ID]={}
    for r2ID in regionsList:
        if r2ID>=r1ID:
            print('Region pairs:',r1ID,r2ID)
            s1=set(df[regionsPDict[r1ID].contains_points(df[['lon','lat']])].PathID)
            s2=set(df[regionsPDict[r2ID].contains_points(df[['lon','lat']])].PathID)
            s=s1&s2
            connectivityG[r1ID-1,r2ID-1]+=len(s)
            dfDict[r1ID][r2ID]=df[df['PathID'].isin(s)]
print(connectivityG)
Region pairs: 1 1
Region pairs: 1 2
Region pairs: 1 3
Region pairs: 1 4
Region pairs: 1 5
Region pairs: 1 6
Region pairs: 1 7
Region pairs: 1 8
Region pairs: 1 9
Region pairs: 1 10
Region pairs: 1 11
Region pairs: 1 12
Region pairs: 2 2
Region pairs: 2 3
Region pairs: 2 4
Region pairs: 2 5
Region pairs: 2 6
Region pairs: 2 7
Region pairs: 2 8
Region pairs: 2 9
Region pairs: 2 10
Region pairs: 2 11
Region pairs: 2 12
Region pairs: 3 3
Region pairs: 3 4
Region pairs: 3 5
Region pairs: 3 6
Region pairs: 3 7
Region pairs: 3 8
Region pairs: 3 9
Region pairs: 3 10
Region pairs: 3 11
Region pairs: 3 12
Region pairs: 4 4
Region pairs: 4 5
Region pairs: 4 6
Region pairs: 4 7
Region pairs: 4 8
Region pairs: 4 9
Region pairs: 4 10
Region pairs: 4 11
Region pairs: 4 12
Region pairs: 5 5
Region pairs: 5 6
Region pairs: 5 7
Region pairs: 5 8
Region pairs: 5 9
Region pairs: 5 10
Region pairs: 5 11
Region pairs: 5 12
Region pairs: 6 6
Region pairs: 6 7
Region pairs: 6 8
Region pairs: 6 9
Region pairs: 6 10
Region pairs: 6 11
Region pairs: 6 12
Region pairs: 7 7
Region pairs: 7 8
Region pairs: 7 9
Region pairs: 7 10
In [74]:
for i in range(connectivityG.shape[0]):
    for j in range(i+1,connectivityG.shape[0]):
        connectivityG[j,i]=connectivityG[i,j]
regions=regionsDF.groupby('region_id').mean()
conArgSort=connectivityG.argsort(axis=1)

graphD={i+1:row[-3:-1]+1 for i,row in enumerate(conArgSort)}
graphV={i+1:connectivityG[i,row[-3:-1]] for i,row in enumerate(conArgSort)}

maxEdge=max([max(graphV[k]) for k in graphD.keys()])
minEdge=min([min(graphV[k]) for k in graphD.keys() if k!=3])
scaleD=maxEdge-minEdge
lineL={}
for i in range(1,13):
    lineL[i]=[]
    for k,j in enumerate(graphD[i]):
        lineL[i].append([[regions.loc[i,'lon'],regions.loc[j,'lon']],
                    [regions.loc[i,'lat'],regions.loc[j,'lat']],
                    (graphV[i][k]-minEdge)/scaleD])
        #print(i,k,j,(graphV[i][k]-minEdge)/scaleD)
[[2931  360    0  258   68  179  202   92  154   38  105  242]
 [ 360 1507    0  167    9   86   80   54  113   33   64   72]
 [   0    0    0    0    0    0    0    0    0    0    0    0]
 [ 258  167    0 1687  111  127  119   74   57   67   33  131]
 [  68    9    0  111  459   10   51   12    1   10    6   22]
 [ 179   86    0  127   10  899  186   22   51    7    4   16]
 [ 202   80    0  119   51  186 1327   72   62   67   23   74]
 [  92   54    0   74   12   22   72  668   55   19   12   40]
 [ 154  113    0   57    1   51   62   55  945   40   42   32]
 [  38   33    0   67   10    7   67   19   40  612  102    8]
 [ 105   64    0   33    6    4   23   12   42  102  752   32]
 [ 242   72    0  131   22   16   74   40   32    8   32 1013]]
{1: array([4, 2]), 2: array([4, 1]), 3: array([10, 11]), 4: array([2, 1]), 5: array([1, 4]), 6: array([1, 7]), 7: array([6, 1]), 8: array([4, 1]), 9: array([2, 1]), 10: array([ 7, 11]), 11: array([10,  1]), 12: array([4, 1])}
{1: array([258, 360]), 2: array([167, 360]), 3: array([0, 0]), 4: array([167, 258]), 5: array([ 68, 111]), 6: array([179, 186]), 7: array([186, 202]), 8: array([74, 92]), 9: array([113, 154]), 10: array([ 67, 102]), 11: array([102, 105]), 12: array([131, 242])}
1 0 4 0.6518771331058021
1 1 2 1.0
2 0 4 0.3412969283276451
2 1 1 1.0
3 0 10 -0.22866894197952217
3 1 11 -0.22866894197952217
4 0 2 0.3412969283276451
4 1 1 0.6518771331058021
5 0 1 0.0034129692832764505
5 1 4 0.15017064846416384
6 0 1 0.3822525597269625
6 1 7 0.4061433447098976
7 0 6 0.4061433447098976
7 1 1 0.46075085324232085
8 0 4 0.023890784982935155
8 1 1 0.08532423208191127
9 0 2 0.15699658703071673
9 1 1 0.29692832764505117
10 0 7 0.0
10 1 11 0.11945392491467577
11 0 10 0.11945392491467577
11 1 1 0.1296928327645051
12 0 4 0.21843003412969283
12 1 1 0.5972696245733788
In [75]:
import plotly.graph_objects as go
import plotly.io as pio

mapbox_access_token="pk.eyJ1IjoibWRraGF0YW1pIiwiYSI6ImNqc2Q3MDBlZzBjdTYzeXRsbmtuN2JmYTAifQ.DmiQnVUdDKAV2G2uZppE3g"


lat=regionsDF.lat.mean()
lon=regionsDF.lon.mean()
jelbiArr=[[52.499258, 13.407773],
[52.554145, 13.416526],
[52.544389, 13.295859],
[52.529869, 13.449394],
[52.453640, 13.385650]]
jelbi=pd.DataFrame(jelbiArr,columns=['lat','lon'])
fig = go.Figure()
fig.add_trace(go.Scattermapbox(
                lat=regionsDF['lat'],
                lon=regionsDF['lon'],
                text=regionsDF['region_id'],
                mode='markers',     
                marker=go.scattermapbox.Marker(size=3),
                name='df2'))
fig.add_trace(go.Scattermapbox(
                lat=regions['lat'],
                lon=regions['lon'],
                mode='markers',     
                marker=go.scattermapbox.Marker(size=10),
                name='df2'))

fig.add_trace(go.Scattermapbox(
                lat=jelbi['lat'],
                lon=jelbi['lon'],
                mode='markers',     
                marker=go.scattermapbox.Marker(size=10),
                name='df2'))
for i in range(1,13):
    if i!=3:
        for k in [0,1]:
            if lineL[i][k][2]<0.4:
                linewidth=2
            elif lineL[i][k][2]<0.6:
                linewidth=4
            else:
                linewidth=6
                
            fig.add_trace(go.Scattermapbox(
                mode = "lines",
                lon = lineL[i][k][0],
                lat = lineL[i][k][1],
                line=dict(color='red',width=linewidth)))

fig.update_layout(
    showlegend=False,
    hovermode='closest',
    dragmode='zoom',
    height=1500,
    mapbox=go.layout.Mapbox(
        accesstoken=mapbox_access_token,
        bearing=0,
        center=go.layout.mapbox.Center(
            lat=lat,
            lon=lon
        ),
        pitch=0,
        zoom=10
    )
)

fig.show()